
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/17/2011 15:57:57
-- Generated from EDMX file: C:\Users\jmclachl\Documents\Visual Studio 2010\Projects\LocatorHg\BusinessEntities\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Drinks2];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_PersonalLocations_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PersonalLocations] DROP CONSTRAINT [FK_PersonalLocations_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_Products2_Containers]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products2] DROP CONSTRAINT [FK_Products2_Containers];
GO
IF OBJECT_ID(N'[dbo].[FK_Products2_Substances]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products2] DROP CONSTRAINT [FK_Products2_Substances];
GO
IF OBJECT_ID(N'[dbo].[FK_Products2AndUsers_Products2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products2AndUsers] DROP CONSTRAINT [FK_Products2AndUsers_Products2];
GO
IF OBJECT_ID(N'[dbo].[FK_Products2AndUsers_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products2AndUsers] DROP CONSTRAINT [FK_Products2AndUsers_Users];
GO
IF OBJECT_ID(N'[dbo].[FK_StoresAndUsers_StoreInformation]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StoresAndUsers] DROP CONSTRAINT [FK_StoresAndUsers_StoreInformation];
GO
IF OBJECT_ID(N'[dbo].[FK_StoresAndUsers_Users]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StoresAndUsers] DROP CONSTRAINT [FK_StoresAndUsers_Users];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Containers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Containers];
GO
IF OBJECT_ID(N'[dbo].[PersonalLocations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonalLocations];
GO
IF OBJECT_ID(N'[dbo].[Products2]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products2];
GO
IF OBJECT_ID(N'[dbo].[Products2AndUsers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products2AndUsers];
GO
IF OBJECT_ID(N'[dbo].[StoreInformation]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StoreInformation];
GO
IF OBJECT_ID(N'[dbo].[StoresAndUsers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StoresAndUsers];
GO
IF OBJECT_ID(N'[dbo].[Substances]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Substances];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Containers'
CREATE TABLE [dbo].[Containers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [ContainerType] varchar(50)  NULL,
    [NumberOfContainers] int  NOT NULL,
    [ContainerCapacity] decimal(18,4)  NOT NULL
);
GO

-- Creating table 'PersonalLocations'
CREATE TABLE [dbo].[PersonalLocations] (
    [Latitude] decimal(18,13)  NOT NULL,
    [Longitude] decimal(18,13)  NOT NULL,
    [Description] varchar(100)  NOT NULL,
    [LocationId] uniqueidentifier  NOT NULL,
    [UserName] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'Products2'
CREATE TABLE [dbo].[Products2] (
    [ProductNumber] int  NOT NULL,
    [ContainerFormatId] int  NOT NULL,
    [SubstanceId] int  NOT NULL,
    [Price] decimal(19,4)  NULL,
    [CountryOfOrigin] varchar(100)  NULL,
    [Manufacturer] varchar(100)  NULL
);
GO

-- Creating table 'StoreInformation'
CREATE TABLE [dbo].[StoreInformation] (
    [StoreNumber] int  NOT NULL,
    [Address] varchar(50)  NULL,
    [City] varchar(50)  NULL,
    [Latitude] decimal(18,13)  NULL,
    [Longitude] decimal(18,13)  NULL,
    [Hours] varchar(max)  NULL
);
GO

-- Creating table 'Substances'
CREATE TABLE [dbo].[Substances] (
    [Name] varchar(100)  NOT NULL,
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserName] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'Products2AndUsers'
CREATE TABLE [dbo].[Products2AndUsers] (
    [Product2Id] int  NOT NULL,
    [UserName] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'StoresAndUsers'
CREATE TABLE [dbo].[StoresAndUsers] (
    [StoreNumber] int  NOT NULL,
    [UserName] nvarchar(256)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Containers'
ALTER TABLE [dbo].[Containers]
ADD CONSTRAINT [PK_Containers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Description], [UserName] in table 'PersonalLocations'
ALTER TABLE [dbo].[PersonalLocations]
ADD CONSTRAINT [PK_PersonalLocations]
    PRIMARY KEY CLUSTERED ([Description], [UserName] ASC);
GO

-- Creating primary key on [ProductNumber] in table 'Products2'
ALTER TABLE [dbo].[Products2]
ADD CONSTRAINT [PK_Products2]
    PRIMARY KEY CLUSTERED ([ProductNumber] ASC);
GO

-- Creating primary key on [StoreNumber] in table 'StoreInformations'
ALTER TABLE [dbo].[StoreInformation]
ADD CONSTRAINT [PK_StoreInformation]
    PRIMARY KEY CLUSTERED ([StoreNumber] ASC);
GO

-- Creating primary key on [Id] in table 'Substances'
ALTER TABLE [dbo].[Substances]
ADD CONSTRAINT [PK_Substances]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [UserName] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserName] ASC);
GO

-- Creating primary key on [ProductNumber], [UserName] in table 'Products2AndUsers'
ALTER TABLE [dbo].[Products2AndUsers]
ADD CONSTRAINT [PK_Products2AndUsers]
    PRIMARY KEY NONCLUSTERED ([Product2Id], [UserName] ASC);
GO

-- Creating primary key on [StoreNumber], [UserName] in table 'StoresAndUsers'
ALTER TABLE [dbo].[StoresAndUsers]
ADD CONSTRAINT [PK_StoresAndUsers]
    PRIMARY KEY NONCLUSTERED ([StoreNumber], [UserName] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ContainerFormatId] in table 'Products2'
ALTER TABLE [dbo].[Products2]
ADD CONSTRAINT [FK_Products2_Containers]
    FOREIGN KEY ([ContainerFormatId])
    REFERENCES [dbo].[Containers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Products2_Containers'
CREATE INDEX [IX_FK_Products2_Containers]
ON [dbo].[Products2]
    ([ContainerFormatId]);
GO

-- Creating foreign key on [UserName] in table 'PersonalLocations'
ALTER TABLE [dbo].[PersonalLocations]
ADD CONSTRAINT [FK_PersonalLocations_Users]
    FOREIGN KEY ([UserName])
    REFERENCES [dbo].[Users]
        ([UserName])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PersonalLocations_Users'
CREATE INDEX [IX_FK_PersonalLocations_Users]
ON [dbo].[PersonalLocations]
    ([UserName]);
GO

-- Creating foreign key on [SubstanceId] in table 'Products2'
ALTER TABLE [dbo].[Products2]
ADD CONSTRAINT [FK_Products2_Substances]
    FOREIGN KEY ([SubstanceId])
    REFERENCES [dbo].[Substances]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Products2_Substances'
CREATE INDEX [IX_FK_Products2_Substances]
ON [dbo].[Products2]
    ([SubstanceId]);
GO

-- Creating foreign key on [Products2_ProductNumber] in table 'Products2AndUsers'
ALTER TABLE [dbo].[Products2AndUsers]
ADD CONSTRAINT [FK_Products2AndUsers_Products2]
    FOREIGN KEY ([Product2Id])
    REFERENCES [dbo].[Products2]
        ([ProductNumber])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Users_UserName] in table 'Products2AndUsers'
ALTER TABLE [dbo].[Products2AndUsers]
ADD CONSTRAINT [FK_Products2AndUsers_Users]
    FOREIGN KEY ([UserName])
    REFERENCES [dbo].[Users]
        ([UserName])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Products2AndUsers_Users'
CREATE INDEX [IX_FK_Products2AndUsers_Users]
ON [dbo].[Products2AndUsers]
    ([UserName]);
GO

-- Creating foreign key on [StoreInformations_StoreNumber] in table 'StoresAndUsers'
ALTER TABLE [dbo].[StoresAndUsers]
ADD CONSTRAINT [FK_StoresAndUsers_StoreInformation]
    FOREIGN KEY ([StoreNumber])
    REFERENCES [dbo].[StoreInformation]
        ([StoreNumber])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Users_UserName] in table 'StoresAndUsers'
ALTER TABLE [dbo].[StoresAndUsers]
ADD CONSTRAINT [FK_StoresAndUsers_Users]
    FOREIGN KEY ([UserName])
    REFERENCES [dbo].[Users]
        ([UserName])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StoresAndUsers_Users'
CREATE INDEX [IX_FK_StoresAndUsers_Users]
ON [dbo].[StoresAndUsers]
    ([UserName]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------